using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Tools;
using Logger;

namespace ToolWindows
{
    public partial class Infrastructure : UserControl, IRefreshData
    {
        private string current_database = string.Empty;
        private string connectionString = null;

        public string CurrentDatabase
        {
            set
            {
                current_database = value;
            }
        }

        public Infrastructure()
        {
            InitializeComponent();

            RefreshData();
        }

        public void RefreshData()
        {
            connectionString = UtilitySqlTools.Current.ConnectionString();

            if (connectionString == null)
            {
                return;
            }

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + current_database + "];");
                    sb.AppendLine("SELECT   s.name [Service name],");
                    sb.AppendLine("         s.service_id ");
                    sb.AppendLine("FROM sys.services s ");
                    sb.AppendLine("LEFT JOIN sys.database_principals dp on s.principal_id = dp.principal_id");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    dgvInfServices.DataSource = ds.Tables[0];
                    dgvInfServices.Columns["service_id"].Visible = false;

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }
        }

        private void InfrastructureQueues(object service_id)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + current_database + "];");
                    sb.AppendLine("SELECT   sq.name [Queue name],");
                    sb.AppendLine("         sq.type_desc [Description], ");
                    sb.AppendLine("         sq.max_readers [Max readers], ");
                    sb.AppendLine("         sq.activation_procedure [Activation procedure], ");
                    sb.AppendLine("         dp.name [Execute as principal], ");
                    sb.AppendLine("         case sq.is_activation_enabled when 1 then 'Yes' else null end as [Is activation enabled?],");
                    sb.AppendLine("         case sq.is_receive_enabled when 1 then 'Yes' else null end as [Is receive enabled?],");
                    sb.AppendLine("         case sq.is_enqueue_enabled when 1 then 'Yes' else null end as [Is enqueue enabled?],");
                    sb.AppendLine("         case sq.is_retention_enabled when 1 then 'Yes' else null end as [Is retention enabled?]");
                    sb.AppendLine("FROM sys.service_queue_usages squ ");
                    sb.AppendLine("INNER JOIN sys.service_queues sq ON squ.service_queue_id = sq.object_id ");
                    sb.AppendLine("LEFT JOIN sys.database_principals dp on sq.execute_as_principal_id = dp.principal_id");
                    sb.AppendLine("WHERE squ.service_id = " + service_id.ToString());
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    dgvInfQueues.DataSource = ds.Tables[0];

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }
        }

        private void InfrastructureContracts(object service_id)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("USE [" + current_database + "];");
                    sb.AppendLine("SELECT   sc.name [Contract name],");
                    sb.AppendLine("         dp.name [Principal], ");
                    sb.AppendLine("         sc.service_contract_id");
                    sb.AppendLine("FROM sys.service_contract_usages scu ");
                    sb.AppendLine("INNER JOIN sys.service_contracts sc ON scu.service_contract_id = sc.service_contract_id ");
                    sb.AppendLine("LEFT JOIN sys.database_principals dp on sc.principal_id = dp.principal_id");
                    sb.AppendLine("WHERE scu.service_id = " + service_id.ToString());
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    dgvInfContracts.DataSource = ds.Tables[0];
                    dgvInfContracts.Columns["service_contract_id"].Visible = false;

                    if (dgvInfContracts.Rows.Count == 0)
                    {
                        InfrastructureMessageTypes(0);
                    }

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }
        }

        private void dgvInfServices_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            object service_id = dgvInfServices.Rows[e.RowIndex].Cells["service_id"].Value;

            InfrastructureQueues(service_id);
            InfrastructureContracts(service_id);
        }

        private void dgvInfQueues_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
        }

        private void InfrastructureMessageTypes(object service_contract_id)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + current_database + "];");
                    sb.AppendLine("SELECT   smt.name [Message type name],");
                    sb.AppendLine("         case scmu.is_sent_by_initiator when 1 then 'Yes' else null end [Is sent by initiator?], ");
                    sb.AppendLine("         case scmu.is_sent_by_target when 1 then 'Yes' else null end [Is sent by target?], ");
                    sb.AppendLine("         dp.name [Principal], ");
                    sb.AppendLine("         smt.validation_desc [Validation],");
                    sb.AppendLine("         smt.xml_collection_id,");
                    sb.AppendLine("         (select name from sys.xml_schema_collections WHERE xml_collection_id = smt.xml_collection_id) [XML schema collection]");
                    sb.AppendLine("FROM sys.service_contract_message_usages scmu ");
                    sb.AppendLine("INNER JOIN sys.service_message_types smt ON scmu.message_type_id = smt.message_type_id ");
                    sb.AppendLine("LEFT JOIN sys.database_principals dp on smt.principal_id = dp.principal_id");
                    sb.AppendLine("WHERE scmu.service_contract_id = " + service_contract_id.ToString());
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    dgvInfMessages.DataSource = ds.Tables[0];
                    dgvInfMessages.Columns["xml_collection_id"].Visible = false;

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }
        }

        private void dgvInfContracts_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            object service_contract_id = dgvInfContracts.Rows[e.RowIndex].Cells["service_contract_id"].Value;

            InfrastructureMessageTypes(service_contract_id);
        }

        private string XmlSchemaCollection(object principal, object schema_collection)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + current_database + "];");
                    sb.AppendLine("declare @schema_owner sysname;");
                    sb.AppendLine("select @schema_owner = s.name ");
                    sb.AppendLine("from sys.xml_schema_collections x ");
                    sb.AppendLine("inner join sys.schemas s on x.schema_id = s.schema_id ");
                    sb.AppendLine("where x.name = @SchemaName;");
                    sb.AppendLine("select xml_schema_namespace(@schema_owner,@SchemaName) as text;");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Parameters.AddWithValue("@dbo", principal);
                    da.SelectCommand.Parameters.AddWithValue("@SchemaName", schema_collection);
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        return ds.Tables[0].Rows[0].ItemArray[0].ToString();
                    }

                    connection.Close();

                    return string.Empty;
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return string.Empty;
        }

        private void dgvInfMessages_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
        }

    }
}
